import pymysql
from ToDo.todoAppwithMysql.todo import Todo

class TodoStorgeWithMySQL:
    # 返回函数的静态方法 无需返回
    @staticmethod
    def get_connection():
        # 打开数据库的连接 # autocommit=True   mysql自动提交
        return pymysql.Connection(host='192.168.207.132',database='TEST1',user='root',password='root@123ABC',autocommit=True)

    # 初始化数据库
    def init_db(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            # 如果表不存在则创建
            cursor.execute('''
                CREATE table IF NOT EXISTS `todos`(
                id int PRIMARY KEY auto_increment,
                `title` varchar(200) not null DEFAULT "",
                `completed` int not null DEFAULT 0)
            ''')

    def creat(self,title:str):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            # 插入数据必须为元组类型 (title,)
            # 插入一条值
            cursor.execute("insert into todos(title) values('%s')"%(title,))
            # 获取最近insert记录的自增字段值
            cursor.execute("select last_insert_id()")
            # # 元组中只有一个数据类型时加 ， 表明为元组 （title,）
            (id,) = cursor.fetchone()
            return Todo(title,id=id)

    def convert_row_to_todo(self,row):
        # 转化为object对象
        return Todo(row[1],id=row[0],completed=True if row[2]==1 else False)

    def get_list(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("select * from todos")
            data=cursor.fetchall()
            # 元组嵌套元组
            # 定义空的object对象列表
            list=[]
            # 循环遍历，将每行追加到列表中
            for row in data:
                list.append(self.convert_row_to_todo(row))
            # return [x.__dict__ for x  in list]
            return list

    def get_byid(self,id):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute("select * from todos where id=%s",(id,))
            todo=cursor.fetchone()
            if todo is not None:
                return self.convert_row_to_todo(todo)
            else:
                return None

    def mark_completed(self,id):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute("update todos set completed=1 where id=%s",(id,))
            conn.commit()
            return self.get_byid(id)

    def delete(self,id):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute("delete from todos where id=%s",(id,))
            conn.commit()
            return self.get_list()


    def yescompleted(self):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute("select * from todos where completed=1")
            yeslist = []
            data=cursor.fetchall()
            # 循环遍历，将每行追加到列表中
            for row in data:
                yeslist.append(self.convert_row_to_todo(row))
            return yeslist

    def nofinished(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("select * from todos where completed=0")
            nolist = []
            data = cursor.fetchall()
            # 循环遍历，将每行追加到列表中
            for row in data:
                nolist.append(self.convert_row_to_todo(row))
            return nolist


# manager=TodoStorgeWithMySQL()
# # manager.init_db()
# manager.creat("虚拟机创建")
# # manager.delete(5)